﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tool.Log;

namespace WinFormsScy
{
    class MySqlHelper
    {
        private static readonly string connstr =
            ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

        public static MySqlConnection CreateConnection()
        {
            //using (MySqlConnection conn = new MySqlConnection(connstr))
            MySqlConnection conn = new MySqlConnection(connstr);
            conn.Open();
            return conn;
        }

        public static int ExecuteNonQuery(MySqlConnection conn, string sql,
            params MySqlParameter[] parameters)
        {
            using (MySqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                /*
                foreach (MySqlParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }*/
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteNonQuery();
            }
        }

        public static int ExecuteNonQuery(string sql,
            params MySqlParameter[] parameters)
        {
            using (MySqlConnection conn = CreateConnection())
            {
                return ExecuteNonQuery(conn, sql, parameters);
            }
        }

        public static object ExecuteScalar(MySqlConnection conn, string sql,
            params MySqlParameter[] parameters)
        {
            using (MySqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteScalar();
            }
        }

        public static object ExecuteScalar(string sql,
           params MySqlParameter[] parameters)
        {
            using (MySqlConnection conn = CreateConnection())
            {
                return ExecuteScalar(conn, sql, parameters);
            }
        }

        public static DataTable ExecuteQuery(MySqlConnection conn, string sql,
           params MySqlParameter[] parameters)
        {
            DataTable table = new DataTable();
            using (MySqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(parameters);
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    table.Load(reader);
                }
            }
            return table;
        }

        public static DataTable ExecuteQuery(string sql,
           params MySqlParameter[] parameters)
        {
            using (MySqlConnection conn = CreateConnection())
            {
                return ExecuteQuery(conn, sql, parameters);
            }
        }

        public static int ExecuteTransactionInsert(Dictionary<string, object> ss) 
        {


            int result1 = 0;

            using (MySqlConnection conn = CreateConnection())
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                MySqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    
                    string SBBM = ConfigurationManager.AppSettings["SBBM"];
                    int GDLX_FJX = Convert.ToInt32(ConfigurationManager.AppSettings["GDLX_FJX"]);
                    
                    DataTable dt = ExecuteQuery("select * from task where YCID = " + ss["YCID"]);
                    int tasknum = dt.Rows.Count;
                    if (tasknum == 0)
                    {
                        string settask = "insert into task(YCID,YCGG,WLBM,SBBM,YCCZ,YCCD,HEAD_CUT_TYPE,STATUS,CREATE_TIME) values('" + ss["YCID"] + "','" + ss["YCGG"] + "','" + ss["WLBM"] + "','" + SBBM + "','" + ss["YCCZ"] + "','" + ss["YCCD"] + "','" + ss["HEAD_CUT_TYPE"] + "','0" + "',now())";
                        cmd.CommandText = settask;
                        int sutask = result1 = cmd.ExecuteNonQuery();
                        LogHelper.WriteLog("原材料ID:" + ss["YCID"] + "插入成功");
                    }
                    else
                    {

                        throw new Exception("原材料ID:" + ss["YCID"] + "已存在，下发任务失败！");
                        //string uptask = "update task set YCGG = '" + so["YCGG"] + "',WLBM = '" + so["WLBM"] + "',SBBM = '" + "设备编码" + "',YCCZ = '" + so["YCCZ"] + "',YCCD = '" + so["YCCD"] + "',STATUS = '0' where YCID = '" + so["YLID"] + "'";
                    }

                    List<Dictionary<string, string>> delist = (List<Dictionary<string, string>>)ss["detail"];
                    foreach (Dictionary<string, string> des in delist)
                    {
                        Console.WriteLine("----" + des["ZWLBM"] + " " + des["GCBH"] + " " + des["GYGC"] + " " + des["QGCD"] + " " + des["GZNR"]);
                        string getyxj = "select * from gy where GZNR = '" + des["GZNR"] + "'";
                        DataTable gy = ExecuteQuery(getyxj);
                        int yxj = 0;
                        if (gy.Rows.Count > 0)
                        {
                             yxj = Convert.ToInt32(gy.Rows[0]["YXJ"].ToString());

                        }
                        else
                        {
                            throw new Exception("没有对应工艺代码，请确认工艺代码是否有误");
                           
                        }
                       
                        int GDLX = 1;
                        if (Convert.ToInt32(des["QGCD"]) > GDLX_FJX)
                        {
                            GDLX = 2;
                        }
                        string setdetail = "insert into task_detail(YCID,GYDM,QGCD,ZWLBM,STATUS,GDLX,YXJ,GCBH,GYGC,ITM,CUT_TYPE) values('" + ss["YCID"] + "','" + des["GZNR"] + "','" + des["QGCD"] + "','" + des["ZWLBM"] + "','" + "1" + "'," + GDLX + ",'" + yxj + "','" + des["GCBH"] + "','" + des["GYGC"] + "','" + des["ITM"] + "','" + des["CUT_TYPE"] + "')";
                        cmd.CommandText = setdetail;
                        int sudetail = cmd.ExecuteNonQuery();
                    }
                    tx.Commit();
                }
                catch (Exception ex)
                {
                    LogHelper.WriteLog("出错啦！！！" + ex.Message);
                    tx.Rollback();
                    throw new Exception(ex.Message);
                    //return 0; ;
                }
            }



                return result1;
            
           
        }
    }
}
